/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BL;

import Bean.Nganh;
import Util.DBConnection;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author amekaze
 */
public class BLHeDaoTao {
    
    public List<Nganh> getDSNganh(String MaLoaiHinhDT)throws SQLException, FileNotFoundException, IOException
    {
        List<Nganh> LN = new ArrayList<Nganh>();
        Connection conn =DBConnection.getConnection(); 
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            String sql="SELECT N.manganh, N.tennganh, k.tenkhoa, t.tentruong, t.logotruong, N.gioithieunganh, N.dieukienxettuyen"
                        +" FROM nganh N, Truong t, Khoa k"
                        +" WHERE N.Makhoa = k.MaKhoa"
                        +" AND k.Matruong = t.matruong"
                        +" AND N.Malhdt ="+MaLoaiHinhDT;
            ResultSet rs=stm.executeQuery(sql);
            int i=0;
            while(rs.next())
            {
                Nganh Nganhs=new Nganh();
                Nganhs.setMaNganh(rs.getInt("N.MaNganh"));
                Nganhs.setTenNganh(rs.getString("N.TenNganh"));
                Nganhs.setTenKhoa(rs.getString("k.TenKhoa"));
                Nganhs.setTenTruong(rs.getString("t.TenTruong"));
                Nganhs.setLogoTruong("D:\\Logo"+i+".png");            
                File image = new File("D:\\Logo"+(i++)+".png");
                FileOutputStream fos = new FileOutputStream(image);
                byte[] buffer = new byte[1];
                InputStream is = rs.getBinaryStream("t.logotruong");
                while (is.read(buffer) > 0){
                    fos.write(buffer);
                }
                Nganhs.setGioiThieuNganh(rs.getString("N.GioiThieuNganh"));
                Nganhs.setDieuKienXetTuyen(rs.getString("N.DieuKienXetTuyen"));
                LN.add(Nganhs);            
            }
        }
        conn.close();        
        return LN;
    }
    public Nganh getNganh(String MaLoaiHinhDT) throws SQLException, FileNotFoundException, IOException{
        Connection conn =DBConnection.getConnection(); 
        Statement stm=(Statement) conn.createStatement();
        String sql="SELECT n.manganh, n.tennganh, k.tenkhoa, t.tentruong, t.logotruong, n.gioithieunganh, n.dieukienxettuyen"
                    +" FROM nganh N, Truong t, Khoa k"
                    +" WHERE N.Makhoa = k.MaKhoa"
                    +" AND k.Matruong = t.matruong"
                    +" AND N.Malhdt ="+MaLoaiHinhDT;
        ResultSet rs=stm.executeQuery(sql);
        int i=0;
        Nganh Nganhs=new Nganh();
        if(rs.next())
        {
            
            Nganhs.setMaNganh(rs.getInt("MaNganh"));
            Nganhs.setTenNganh(rs.getString("TenNganh"));
            Nganhs.setTenKhoa(rs.getString("TenKhoa"));
            Nganhs.setTenTruong(rs.getString("TenTruong"));
//            Nganhs.setLogoTruong("D:\\Logo"+i+".png");            
//            File image = new File("D:\\Logo"+(i++)+".png");
//            FileOutputStream fos = new FileOutputStream(image);
//            byte[] buffer = new byte[1];
//            InputStream is = rs.getBinaryStream("logotruong");
//            while (is.read(buffer) > 0){
//                fos.write(buffer);
//            }
            Nganhs.setGioiThieuNganh(rs.getString("GioiThieuNganh"));
            Nganhs.setDieuKienXetTuyen(rs.getString("DieuKienXetTuyen"));
                        
        }
        conn.close();        
        return Nganhs;
    }
    public Nganh getTTNganh(String MaNganh) throws SQLException{
        Connection conn =DBConnection.getConnection(); 
        Statement stm=(Statement) conn.createStatement();
        String sql="SELECT n.manganh, n.tennganh, k.tenkhoa, t.tentruong, t.logotruong, n.gioithieunganh, n.dieukienxettuyen"
                    +" FROM nganh N, Truong t, Khoa k"
                    +" WHERE N.Makhoa = k.MaKhoa"
                    +" AND k.Matruong = t.matruong"
                    +" AND N.manganh ="+MaNganh;
        ResultSet rs=stm.executeQuery(sql);
        int i=0;
        Nganh Nganhs=new Nganh();
        if(rs.next())
        {
            
            Nganhs.setMaNganh(rs.getInt("MaNganh"));
            Nganhs.setTenNganh(rs.getString("TenNganh"));
            Nganhs.setTenKhoa(rs.getString("TenKhoa"));
            Nganhs.setTenTruong(rs.getString("TenTruong"));
//            Nganhs.setLogoTruong("D:\\Logo"+i+".png");            
//            File image = new File("D:\\Logo"+(i++)+".png");
//            FileOutputStream fos = new FileOutputStream(image);
//            byte[] buffer = new byte[1];
//            InputStream is = rs.getBinaryStream("logotruong");
//            while (is.read(buffer) > 0){
//                fos.write(buffer);
//            }
            Nganhs.setGioiThieuNganh(rs.getString("GioiThieuNganh"));
            Nganhs.setDieuKienXetTuyen(rs.getString("DieuKienXetTuyen"));
                        
        }
        conn.close();        
        return Nganhs;
    }
    public String getMaTruong(int MaNganh) throws SQLException{
        Connection conn =DBConnection.getConnection(); 
        Statement stm=(Statement) conn.createStatement();
        String sql="SELECT Distinct(T.MATRUONG) FROM nganh N, truong T, khoa K "
                + "WHERE K.MaTruong=T.MaTruong and N.MaKhoa=K.MaKhoa and N.MaNganh="+MaNganh;
        ResultSet rs=stm.executeQuery(sql);
        String ma=null;
        if(rs.next()){
            ma=rs.getString("MATRUONG");
        }
        conn.close();
        return ma;
    }
    public List<String> getMaLoaiHinhDT(int MaHeDT) throws SQLException
    {
        List<String> ListMaLHDT=new ArrayList<String>();
        Connection conn =DBConnection.getConnection(); 
        Statement stm=(Statement) conn.createStatement();
        String sql="Select Malhdt From LoaiHinhDaoTao where MaHeDT="+MaHeDT;
        ResultSet rs=stm.executeQuery(sql);
        while(rs.next())
        {
            String i;
            i=rs.getString("Malhdt");
            ListMaLHDT.add(i);
        }
        conn.close();
        return ListMaLHDT;
    }
    public List<String> getTenLoaiHinhDT(int MaHeDT) throws SQLException{
        List<String> ListMaLHDT=new ArrayList<String>();
        Connection conn =DBConnection.getConnection(); 
        Statement stm=(Statement) conn.createStatement();
        String sql="Select TenLHDT From LoaiHinhDaoTao where MaHeDT="+MaHeDT;
        ResultSet rs=stm.executeQuery(sql);
        while(rs.next())
        {
            String i;
            i=rs.getString("TenLHDT");
            ListMaLHDT.add(i);
        }
        conn.close();
        return ListMaLHDT;
    }
    
   
}
